-- Drop all CS fulltext index catalogs on the current database
-- if they have the wrong name
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @script nvarchar(4000);
--Drop all fulltext indexes if they exists
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
END
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
END
DECLARE @catalog sysname;
DECLARE Curs CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [name] FROM dbo.sysfulltextcatalogs
OPEN Curs;
FETCH NEXT FROM Curs INTO @catalog
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Ver = 2005
SELECT @script = N'DROP FULLTEXT CATALOG [' + @Catalog + ']'
ELSE
SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''drop'';';
EXECUTE sp_executesql @script;
FETCH NEXT FROM Curs INTO @catalog
END
CLOSE Curs;
DEALLOCATE Curs;
GO
--The content fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
IF @Ver = 2005
SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']'
ELSE
SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';
EXECUTE sp_executesql @script;
END
--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_modules_content'),N'ContentBinary',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_modules_content;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_modules_content', N'drop';
END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_CS_ContentBinary';
IF @Ver = 2005
BEGIN
SELECT @script = N'
CREATE FULLTEXT INDEX ON [dbo].[tbl_modules_content]
(
[ContentBinary] TYPE COLUMN [Fil_Ext] LANGUAGE ''Swedish'',
[introduction] LANGUAGE ''Swedish'',
[Keywords] LANGUAGE ''Swedish''
)
KEY INDEX [PK_tbl_modules_content] ON [' + @Catalog + '] WITH CHANGE_TRACKING MANUAL;';
EXECUTE sp_executesql @script;
SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_modules_content] ENABLE;';
EXECUTE sp_executesql @script;
END
ELSE
BEGIN
EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'create', @Catalog, N'PK_tbl_modules_content'
EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'introduction', N'add', 0
EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'Keywords', N'add', 0
EXECUTE sp_fulltext_column N'[dbo].[tbl_modules_content]', N'ContentBinary', N'add', 0, N'Fil_ext'
EXECUTE sp_fulltext_table N'[dbo].[tbl_modules_content]', N'activate';
END
GO
--The Xml-index fulltext column
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = N'ftxt_' + CAST(DB_ID(DB_NAME()) AS nvarchar(10)) + N'_XML_Data'
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = @Catalog)
BEGIN
IF @Ver = 2005
SELECT @script = N'CREATE FULLTEXT CATALOG [' + @Catalog + ']' /*+
N'IN PATH ''E:\SQL Server 2005\FTDATA'';';*/
ELSE
SELECT @script = N'EXECUTE sp_fulltext_catalog [' + @Catalog + '], N''create'';';
EXECUTE sp_executesql @script;
END
--drop the index if existing
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.tbl_Xml_data'),N'FullData',N'IsFulltextIndexed') = 1
BEGIN
IF @Ver = 2005
EXECUTE sp_executesql N'DROP FULLTEXT INDEX ON dbo.tbl_Xml_data;';
ELSE
EXECUTE sp_fulltext_table N'dbo.tbl_Xml_data', N'drop';
END
GO
DECLARE @Ver int;
IF(CHARINDEX ('9.00.', @@VERSION) > 0)
SELECT @Ver = 2005;
ELSE
SELECT @Ver = 2000;
DECLARE @Catalog sysname;
DECLARE @script nvarchar(3000);
SELECT @Catalog = 'ftxt_' + CAST(DB_ID() AS nvarchar(10)) + N'_XML_Data';
IF @Ver = 2005
BEGIN
SELECT @script = N'
CREATE FULLTEXT INDEX ON [dbo].[tbl_xml_data]
(
[FullData] LANGUAGE ''Swedish''
)
KEY INDEX [PK_tbl_xml_data] ON [' + @Catalog + '] WITH CHANGE_TRACKING AUTO;';
EXECUTE sp_executesql @script;
SELECT @script = N'ALTER FULLTEXT INDEX ON [dbo].[tbl_xml_data] ENABLE;';
EXECUTE sp_executesql @script;
END
ELSE
BEGIN
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'create', @Catalog, N'PK_tbl_xml_data';
EXECUTE sp_fulltext_column N'[dbo].[tbl_xml_data]', N'FullData', N'add', 0;
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'activate';
--start background index update
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_change_tracking';
EXECUTE sp_fulltext_table N'[dbo].[tbl_xml_data]', N'Start_background_updateindex';
END